package dbAdapter;



import CommonClass.IMG;
import CommonClass.keyword;
import CommonClass.URLlink;
import CommonClass.URLSearch;




















import java.util.HashMap;
import java.util.HashSet;
import java.util.Map;
import java.util.Map.Entry;
import java.util.Queue;
import java.util.ArrayList;
import java.util.Set;
import java.util.Vector;
import java.sql.*;

import javax.swing.text.html.HTMLDocument.Iterator;

public class DatabaseAdapter {


	private static DatabaseAdapter instance = null;



	private String DATABASE_NAME 			= "imagesearch";
	private String KEYWORD_TABLE	 		= "keyword";
	private String RESULT_TABLE	     		= "result";
	private String TEMP_RESULT_TABLE		= "tempresult";
	private String TEMP_KEYWORD_TABLE 		= "tempkeyword";
	private String SEARCHED_TABLE      		= "searchhistory";
	private String KEYWORD_STATISTICS_TABLE	= "keywordstatistics";
	private String TEMP_SEARCH_TABLE 		= "tempsearchlist";
	private String UNPROCESSED_TABLE		= "unprocessed_searchlist";
	private String DATABASE_USERNAME 		= "root";
	private String DATABASE_PASSWORD 		= "1234";

	private	String KEYWORD					="";
	private	boolean isOccupied				= false;


	private Connection connect				= null;
	private Statement  stmt 				= null;
	private HashSet<String> track_unprocessed_hashkey;

	public DatabaseAdapter() {

		try {
			connectToDatabase();

		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			System.out.println(e.getMessage());
		}

	}


	public static DatabaseAdapter getInstance() {

		if(instance == null) {
			instance = new DatabaseAdapter();
		}

		return instance;

	}

	public synchronized boolean getMutex(){

		if(isOccupied == true){
			return false;
		}
		else{
			isOccupied = true;
			return true;
		}

	}

	public synchronized boolean returnMutex(){

		if(isOccupied == true){
			isOccupied = false;
			return true;
		}
		else{
			return false;

		}
	}

	public synchronized void clearSearch(){

		KEYWORD="";
		clearUnprocessedURLList();
		clearTempSearchTable();
		return;
	}

	public synchronized void setNewSearch(String _keyword, URLlink firstURL){

		KEYWORD = _keyword;
		clearUnprocessedURLList();
		clearTempSearchTable();
		addToSearchedList(firstURL);
		track_unprocessed_hashkey= new HashSet<String>();
		return;

	}

	public void setNewSearch(keyword _keyword, URLlink firstURL){


		KEYWORD = _keyword.getKeyword();
		clearUnprocessedURLList();
		clearTempSearchTable();
		addToSearchedList(firstURL);
		return ;
	}

	public synchronized void addNewUnprocessedUrlList(HashMap<URLlink,Integer> newList){

		clearTempSearchTable();
		java.util.Iterator<Entry<URLlink, Integer>> i = newList.entrySet().iterator(); 
		int j = 0;
		String insertQuery = "insert into "+TEMP_SEARCH_TABLE+" values";
		String selectQuery="";
		while(i.hasNext()){

			Map.Entry entry = (Map.Entry) i.next(); 
			URLlink tmplink = (URLlink) entry.getKey();
			int tmpProbability = ((Integer) entry.getValue()).intValue();

			if(tmplink.getURL().length()<=250){
				if(j == 0){

					insertQuery+="(\""+tmplink.getURL()+"\","+tmpProbability+")";
				}else{

					insertQuery+=",(\""+tmplink.getURL()+"\","+tmpProbability+")";
				}
				j++;
			}	
		}
		try{
			executeUpdateQuery(insertQuery);
		}catch(SQLException e){
		}
		j=0;
		insertQuery="";



		String query = "select * from " + TEMP_SEARCH_TABLE  + " where " + TEMP_SEARCH_TABLE + ".URL NOT IN " +
				"(select " + SEARCHED_TABLE+ ".URL from " + SEARCHED_TABLE +") AND " + 
				TEMP_SEARCH_TABLE + ".URL NOT IN (select "+UNPROCESSED_TABLE+".URL from "+ UNPROCESSED_TABLE+")";




		ResultSet result;
		String query1="insert into " + UNPROCESSED_TABLE +" values";
		try {
			result = executeSelectQuery(query);
			int k=0;

			while(result.next()){

				String tempURL = result.getString("URL");
				int probability = result.getInt("probability");


				if(track_unprocessed_hashkey.add(tempURL.toLowerCase()))
				{
					String addon=addToUnprocessedTable(tempURL,probability);
					if(addon.length()!=0)
					{
						if(k>0)
						{
							query1+=", ";
						}

						query1+=addon;
						k++;
					}

				}



			}
			System.out.println(query1);
			System.out.println(query1.length());
			executeUpdateQuery(query1);
		} catch (SQLException e) {
			// TODO Auto-generated catch block

			e.printStackTrace();
			clearTempSearchTable();	
			return;
		}
		clearTempSearchTable();	

		return;


	}

	public synchronized void addNewUnprocessedURL(URLlink URL,int probability){


		String query = "select * from " + SEARCHED_TABLE +","+ UNPROCESSED_TABLE + 
				" where "+ SEARCHED_TABLE + ".URL=\"" + URL.getURL() + "\" OR " + 
				UNPROCESSED_TABLE + ".URL=\""+ URL.getURL()+"\"";

		ResultSet result;
		if(URL.getURL().length()<=250){
			try {
				result = executeSelectQuery(query);
				if(result == null){
					addToUnprocessedTable(URL.getURL(),probability);

				}

			} catch (SQLException e) {
				// TODO Auto-generated catch block

				e.printStackTrace();
				return;
			}
		}
		clearTempSearchTable();

		return;
	}

	private void insertIntoTempTable(URLlink URL, int probability){

		String query = "insert into " + TEMP_SEARCH_TABLE +" values(\""+URL.getURL()+ "\","+probability+")";
		ResultSet result;
		if(URL.getURL().length()>250){
			return;
		}
		try {
			executeUpdateQuery(query);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			return;
		}
	}

	private void addToSearchedList(URLlink URL){

		String query = "select * from "+ SEARCHED_TABLE+ " where " + SEARCHED_TABLE + ".URL=\""+URL.getURL()+"\"" ;
		ResultSet result;
		try {
			result = executeSelectQuery(query);

			if(!result.next() ){
				query = "insert into " + SEARCHED_TABLE +" values(\""+URL.getURL()+ "\",NOW(),1)";

			}
			else{
				query = "update "+SEARCHED_TABLE+ " set " + SEARCHED_TABLE+".searchedCount =" +
						SEARCHED_TABLE+".searchedCount + 1 where " +SEARCHED_TABLE+".URL=\""+URL.getURL()+"\"";
			}

			executeUpdateQuery(query);


		} catch (SQLException e) {
			// TODO Auto-generated catch block

			e.printStackTrace();
			return;
		}


		return;

	}

	private synchronized String addToUnprocessedTable(String URL, int probability){

		String query = " (\""+URL+"\","+probability+")";
		ResultSet result;
		if(URL.length()>250){
			return "";
		}

		return  query;

	}

	private synchronized void addToUnprocessedTable(URLlink URL, int probability){

		String query = "insert into " + UNPROCESSED_TABLE +" values(\""+URL.getURL()+"\","+probability+")";
		if(URL.getURL().length()>250){
			return;
		}
		ResultSet result;
		try {
			executeUpdateQuery(query);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			return;
		}
		return;

	}

	public synchronized int getNumberOfUnprocessedURL(){

		int size = 0;

		String query = "select count(*) from " + UNPROCESSED_TABLE;
		ResultSet result;
		try {
			result = executeSelectQuery(query);
			if(result.next()){
				size = result.getInt(1);
			}


		} catch (SQLException e) {
			// TODO Auto-generated catch block

			e.printStackTrace();
			return 0;
		}

		return size;
	}

	public synchronized URLlink getUnprocessedURL(){

		String query = "select max("+UNPROCESSED_TABLE+".probability) from " + UNPROCESSED_TABLE;
		String URL = null;
		ResultSet result;
		URLlink tempURL = null;;
		int highest_probability = 0;

		try {
			result = executeSelectQuery(query);

			if(result != null){

				if(result.next()){
					highest_probability = result.getInt(1);
				}

				query = "select * from "+UNPROCESSED_TABLE+
						" where "+UNPROCESSED_TABLE+".probability="+highest_probability+" limit 1";

				result = executeSelectQuery(query);
				if(result!=null){
					if(result.next()){
						URL = result.getString("URL");
						tempURL = new URLlink(URL);
					}
				}
				else{
					return null;
				}

			}
			else{
				return null;
			}

		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			return null;
		}	
		query = "delete from "+ UNPROCESSED_TABLE+ " where URL=\""+ URL+"\"";
		try {
			executeUpdateQuery(query);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			return null;
		}	
		addToSearchedList(tempURL);


		return tempURL;

	}

	private void clearUnprocessedURLList(){

		String query = "delete from " + UNPROCESSED_TABLE;
		ResultSet result;
		try {
			executeUpdateQuery(query);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			return;
		}

		return;

	}

	private void clearTempSearchTable(){

		ResultSet result;
		String query = "delete from " + TEMP_SEARCH_TABLE;
		try {
			executeUpdateQuery(query);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			return;
		}
		return;

	}

	private void clearTempResultTable(){

		ResultSet result;
		String query = "delete from " + TEMP_RESULT_TABLE;
		try {
			executeUpdateQuery(query);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			return;
		}
		return;

	}

	private void clearTempKeywordTable(){

		ResultSet result;
		String query = "delete from " + TEMP_KEYWORD_TABLE;
		try {
			executeUpdateQuery(query);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			return;
		}
		return;

	}

	public synchronized void addResultList(Vector<String> URL, Vector<IMG> image, Vector<keyword> _keyword){


		String insertResultStmt = "insert into " + TEMP_RESULT_TABLE +" values";
		String insertKeywordStmt = "insert into "+ TEMP_KEYWORD_TABLE +" values";
		String resultQuery, keywordQuery, tmpSelectQuery;
		int UrlSize= URL.size();
		int imageSize = image.size();
		int keywordSize = _keyword.size();
		ResultSet keywordResult;
		ResultSet imageResult;
		ResultSet tempResult;
		int resultCount = 0, keywordCount =0;
		if(UrlSize == imageSize && UrlSize == keywordSize){

			for(int i =0;i< UrlSize;i++){

				if(image.get(i).getImageURLlink().length() <=250 && URL.get(i).length()<250){
					if(resultCount == 0){
						insertResultStmt+="(\""+URL.get(i)+"\",\""+image.get(i).getImageURLlink()+"\",0,0)";
					}
					else{
						insertResultStmt+=",(\""+URL.get(i)+"\",\""+image.get(i).getImageURLlink()+"\",0,0)";
					}

					resultCount++;
				}

				if(_keyword.get(i).getKeyword().length()<=250 && image.get(i).getImageURLlink().length()<=250 && URL.get(i).length()<=250){
					if(keywordCount == 0){
						insertKeywordStmt+="(\""+_keyword.get(i).getKeyword()+"\",\""+image.get(i).getImageURLlink()+"\",\""+URL.get(i)+"\",\""+_keyword.get(i).getTag()+"\")";
					}
					else{
						insertKeywordStmt+=",(\""+_keyword.get(i).getKeyword()+"\",\""+image.get(i).getImageURLlink()+"\",\""+URL.get(i)+"\",\""+_keyword.get(i).getTag()+"\")";
					}
					keywordCount++;
				}
			}
			System.out.println("*************************Before add Result");
			try {
				executeUpdateQuery(insertResultStmt);
				executeUpdateQuery(insertKeywordStmt);
			} catch (SQLException e1) {
				// TODO Auto-generated catch block
				e1.printStackTrace();
				System.out.println(e1.getMessage());

			}

			System.out.println("*************************After add Result");
			resultCount =0;
			keywordCount=0;
			insertResultStmt = "insert into " + RESULT_TABLE +" values";
			insertKeywordStmt = "insert into "+ KEYWORD_TABLE +" values";

			resultQuery="select * from "+TEMP_RESULT_TABLE+" group by URL,imageURL"; 
			keywordQuery ="select * from "+TEMP_KEYWORD_TABLE+" group by URL,imageURL,keyword";

			try {
				imageResult = executeSelectQuery(resultQuery);
				while(imageResult.next()){

					tmpSelectQuery = "select * from "+RESULT_TABLE+" where "+RESULT_TABLE+".URL=\""+imageResult.getString("URL")+"\" AND "+
							RESULT_TABLE+".imageURL=\""+imageResult.getString("imageURL")+"\"";
					tempResult = executeSelectQuery(tmpSelectQuery);
					if(!tempResult.next()){
						if(resultCount == 0){
							insertResultStmt+="(\""+imageResult.getString("URL")+"\",\""+imageResult.getString("imageURL")+"\","+imageResult.getInt("height")+","+imageResult.getInt("width")+")";
						}
						else{
							insertResultStmt+=",(\""+imageResult.getString("URL")+"\",\""+imageResult.getString("imageURL")+"\","+imageResult.getInt("height")+","+imageResult.getInt("width")+")";
						}
						resultCount++;
					}
				}

			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
				return;
			}

			try {
				keywordResult = executeSelectQuery(keywordQuery);
				while(keywordResult.next()){

					tmpSelectQuery = "select * from "+KEYWORD_TABLE+" where "+KEYWORD_TABLE+".URL=\""+keywordResult.getString("URL")+"\" AND "+
							KEYWORD_TABLE+".imageURL=\""+keywordResult.getString("imageURL")+"\" AND "+
							KEYWORD_TABLE+".keyword=\""+keywordResult.getString("keyword")+"\"";

					tempResult = executeSelectQuery(tmpSelectQuery);
					if(!tempResult.next()){
						if(keywordCount == 0){
							insertKeywordStmt+="(\""+keywordResult.getString("keyword")+"\",\""+keywordResult.getString("imageURL")+"\",\""+keywordResult.getString("URL")+"\",\""+keywordResult.getString("tag")+"\")";
						}
						else{
							insertKeywordStmt+=",(\""+keywordResult.getString("keyword")+"\",\""+keywordResult.getString("imageURL")+"\",\""+keywordResult.getString("URL")+"\",\""+keywordResult.getString("tag")+"\")";
						}
						keywordCount++;
					}
				}



			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
				return;
			}
			if(resultCount > 0){
				try {
					executeUpdateQuery(insertResultStmt);
				} catch (SQLException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
			}
			if(keywordCount > 0){
				try {
					executeUpdateQuery(insertKeywordStmt);
				} catch (SQLException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
			}
			clearTempResultTable();
			clearTempKeywordTable();


		}
		else{
			System.out.println("Results are not added to the database as the vector sizes are different");
			return;
		}
	}

	public synchronized void addResult(String URL, IMG image, keyword _keyword){

		String query = "insert into " + RESULT_TABLE +
				" values(\""+URL+"\",\""+image.getImageURLlink()+"\",0,0)";
		ResultSet result;
		if(URL.length()>250 || image.getImageURLlink().length()>250 || _keyword.getKeyword().length()>250){
			return;
		}
		try {
			executeUpdateQuery(query);
		} catch (SQLException e1) {
			// TODO Auto-generated catch block
			e1.printStackTrace();
			return;
		}

		query = "insert into " + KEYWORD_TABLE +
				" values(\""+_keyword.getKeyword()+"\",\""+image.getImageURLlink()+"\",\""+URL+"\",\""+_keyword.getTag()+"\")";

		try {
			executeUpdateQuery(query);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			return;
		}

	}

	public Vector<IMG> getImageFromKeyword(String _keyword) {

		int size =0;
		Vector<IMG> tempImageURLList = new Vector<IMG>(); 
		String query = "select * from " + KEYWORD_TABLE +","+ RESULT_TABLE + 
				" where "+ KEYWORD_TABLE + ".URL=" + RESULT_TABLE +".URL AND "+
				KEYWORD_TABLE + ".imageURL=" + RESULT_TABLE +".imageURL AND "+ KEYWORD_TABLE+
				".keyword=\"" +_keyword +"\"";
		ResultSet result;
		try {
			result = executeSelectQuery(query);
			while(result.next()){

				IMG temp = new IMG(result.getString("imageURL"),
						result.getInt("height"),
						result.getInt("width"));

				tempImageURLList.add(temp);

				size++;
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}

		System.out.println(size);
		return tempImageURLList;
	}

	public Vector<IMG> getImageFromURL(URLlink URL){

		int size =0;
		Vector<IMG> tempImageURLList = new Vector<IMG>(); 
		String query = "select * from " + KEYWORD_TABLE +","+ RESULT_TABLE + 
				" where "+ KEYWORD_TABLE + ".URL=" + RESULT_TABLE +".URL AND "+
				KEYWORD_TABLE + ".imageURL=" + RESULT_TABLE +".imageURL AND "+ 
				KEYWORD_TABLE + ".URL=\"" + URL.getURL() +"\"";
		ResultSet result;
		try {
			result = executeSelectQuery(query);
			while(result.next()){

				IMG temp = new IMG(result.getString("imageURL"),
						result.getInt("height"),
						result.getInt("width"));

				tempImageURLList.add(temp);

				size++;
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}


		return tempImageURLList;

	}

	public Vector<keyword> getKeywordFromURL(URLlink url){

		int size =0;
		Vector<keyword> tempKeywordList = new Vector<keyword>();
		String query = "select * from " + KEYWORD_TABLE + " where URL=\"" + url.getURL() +"\"";
		ResultSet result;
		try {
			result = executeSelectQuery(query);
			while(result.next()){

				keyword temp = new keyword(result.getString("tag"),
						result.getString("keyword") );

				tempKeywordList.add(temp);

				size++;
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}

		System.out.println(size);


		return tempKeywordList;
	}

	public Vector<keyword> getKeywordFromImage(IMG image){

		int size = 0;
		Vector<keyword> tempKeywordList = new Vector<keyword>();
		String query = "select * from " + KEYWORD_TABLE + " where imageURL=\"" + image.getImageURLlink()+"\"";
		ResultSet result;
		try {
			result = executeSelectQuery(query);
			while(result.next()){

				keyword temp = new keyword(result.getString("tag"),
						result.getString("keyword"));

				tempKeywordList.add(temp);

				size++;
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}




		return tempKeywordList;

	}

	public Vector<URLlink> getURLlinkFromImageURL(IMG imageURL){

		int size =0;
		Vector<URLlink> tempURLlinkList = new Vector<URLlink>();
		String query = "select * from " + RESULT_TABLE + " where imageURL=\"" + imageURL.getImageURLlink() +"\"";
		ResultSet result;
		try {
			result = executeSelectQuery(query);
			while(result.next()){

				URLlink temp = new URLlink(result.getString("URL"));

				tempURLlinkList.add(temp);

				size++;
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}




		return tempURLlinkList;

	}

	public Vector<URLlink> getURLlinkFromKeyword(String _keyword){

		int size =0;
		Vector<URLlink> tempURLlinkList = new Vector<URLlink>();
		String query = "select * from " + KEYWORD_TABLE + " where keyword=\"" + _keyword +"\"";
		ResultSet result;
		try {
			result = executeSelectQuery(query);
			while(result.next()){

				URLlink temp = new URLlink(result.getString("URL"));

				tempURLlinkList.add(temp);

				size++;
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			return null;
		}



		return tempURLlinkList;

	}

	public Vector<IMG> getImageFromSimilarKeyword(String keyword){

		Vector<IMG> tempImageURLList = new Vector<IMG>(); 
		String query = "select * from " + KEYWORD_TABLE +","+ RESULT_TABLE + 
				" where "+ KEYWORD_TABLE + ".URL=" + RESULT_TABLE +".URL AND "+
				KEYWORD_TABLE + ".imageURL=" + RESULT_TABLE +".imageURL AND "+ KEYWORD_TABLE +
				".keyword LIKE \"%" + keyword +"%\"";
		ResultSet result;
		try {
			result = executeSelectQuery(query);
			while(result.next()){

				IMG temp = new IMG(result.getString("imageURL"),
						result.getInt("height"),
						result.getInt("width"));

				tempImageURLList.add(temp);


			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			return null;
		}

		return tempImageURLList;


	}

	public Vector<URLlink> getURLlinkFromSimilarKeyword(String keyword){

		int size =0;
		Vector<URLlink> tempURLlinkList = new Vector<URLlink>();
		String query = "select * from " + KEYWORD_TABLE + " where keyword LIKE \"%" + keyword +"%\"";
		ResultSet result;
		try {
			result = executeSelectQuery(query);
			while(result.next()){

				URLlink temp = new URLlink(result.getString("URL"));

				tempURLlinkList.add(temp);

				size++;
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			return null;
		}




		return tempURLlinkList;


	}

	public Vector<IMG> getIMGFromMatchingUrlAndKeyword(String _keyword, String urlLink){

		Vector<IMG> tempImageURLList = new Vector<IMG>(); 
		String query = "select * from " + KEYWORD_TABLE +","+ RESULT_TABLE + 
				" where "+ KEYWORD_TABLE + ".URL=" + RESULT_TABLE +".URL AND "+
				KEYWORD_TABLE + ".imageURL=" + RESULT_TABLE +".imageURL AND "+
				RESULT_TABLE +".URL=\""+urlLink+"\" AND "+
				KEYWORD_TABLE +".keyword LIKE \"%" + _keyword +"%\"";
		ResultSet result;
		try {
			result = executeSelectQuery(query);
			while(result.next()){

				IMG temp = new IMG(result.getString("imageURL"),
						result.getInt("height"),
						result.getInt("width"));

				tempImageURLList.add(temp);


			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			return null;
		}

		return tempImageURLList;

	}

	public boolean isKeywordExisted(keyword _keyword){

		String query = "select * from " + KEYWORD_TABLE + " where keyword=\"" + _keyword.getKeyword() +"\"";
		ResultSet result;
		try {
			result = executeSelectQuery(query);
			if(!result.next())
			{
				return false;
			}
			else
			{
				return true;
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			return false;
		}

	}

	public boolean isKeywordExisted(String keyword){

		String query = "select * from " + KEYWORD_TABLE + " where keyword=\"" + keyword +"\"";
		ResultSet result;
		try {
			result = executeSelectQuery(query);
			if(!result.next())
			{
				return false;
			}
			else
			{
				return true;
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			return false;
		}
	}

	private void connectToDatabase() throws SQLException {


		try {
			Class.forName("com.mysql.jdbc.Driver");
			System.out.println("Connected to database");
		} catch (ClassNotFoundException e) {
			// TODO Auto-generated catch block
			System.out.println("Unable to connect to database connection");
		} // Setup the connection with the DB

		try {
			connect = DriverManager.getConnection("jdbc:mysql://localhost/"+DATABASE_NAME,
					DATABASE_USERNAME,
					DATABASE_PASSWORD);


			System.out.println("Connected to database connection");


		} catch (SQLException e) {

			System.out.println("Unable to connect to database");
		}

	}

	private ResultSet executeSelectQuery(String query) throws SQLException{

		try {
			stmt = connect.createStatement();
			ResultSet result = stmt.executeQuery(query);
			System.out.println("Select Query is successful");

			return result;


		} catch (SQLException e) {
			// TODO Auto-generated catch block
			System.out.println("Select Query is unsuccessful");
			System.out.println(e.getMessage());
			return null;
		}

	}

	private void executeUpdateQuery(String query) throws SQLException {

		try{

			stmt = connect.createStatement();
			int i = stmt.executeUpdate(query);
			System.out.println("Update Query is successful");
			System.out.println("Row modified: "+ i);

		} catch (SQLException e) {
			// TODO Auto-generated catch block
			System.out.println("Update Query is unsuccessful");
			System.out.println(e.getMessage());
			return;
		}

	}


	private int getQueryResultSize(ResultSet result) throws SQLException{

		int size = 0;

		if (result != null)   
		{  
			result.beforeFirst();  
			result.last();  
			size = result.getRow();  
		}  
		return size;
	}

}
